
import pandas as pd
import time
from ScoreTransformer import *
def convert_feature_type(dff,dfsc):
    '''
    :param dff dfsc :  特征数据 分箱赋分表
    :return: dff 转换数据类型后的特征数据
    '''
    type = dfsc[['var_name', 'type']].drop_duplicates()

    cate = type.loc[type.type.isin(['布尔型', '分档型']), 'var_name'].to_list()
    cont = type.loc[type.type.isin(['连续型']), 'var_name'].to_list()

    dff[cont] = dff[cont].astype('float')
    dff[cate] = dff[cate].astype('str')
    return dff

def get_model_type_list(dfsc):
    '''
    :param dfsc: 分箱表
    :return: 模型类型的列表
    '''
    lst = dfsc.model_type.to_list()
    model_type_lst = list(set(lst))
    model_type_lst.remove('all')
    return model_type_lst




def get_esg_score_cut(dfs_cut,dfwt_cut):
    dfs_cut_z = dfs_cut.set_index(['pjuuid','model_type']).stack().reset_index()
    dfs_cut_z.columns = ['pjuuid','model_type','var_name','sc']

    sc_wts= pd.merge(dfs_cut_z,dfwt_cut[['var_name','wts','fx']],on='var_name',how='right')

    sc_wts['score'] = sc_wts.sc * sc_wts.wts

    sc_wts['score'] = sc_wts['score'] * 25


    esg_score_cut = sc_wts.set_index(['pjuuid','model_type','var_name']).score.unstack().astype('float')
    esg_score_cut['esg_score']  = esg_score_cut.sum(axis=1,numeric_only=True) + 25

    return esg_score_cut.reset_index()


def get_rl_score(dfs_cut,dff_cut):
    print('开始计算认绿分数')
    dfs_cut['rl_score'] = dfs_cut.lyzb * 50

    dfs_cut.loc[dff_cut.lyzb <= 0.5,'rl_score'] = dfs_cut.ynqd * 50  # dff_cut 要与 dfs_cut 顺序相同
    print('认绿分数计算完毕')
    return  dfs_cut[['pjuuid','rl_score']]


def get_level(dfs):
    result = pd.DataFrame()
    dfs['pjjg'] = pd.NA
    config_table = pd.read_excel('./data/config_table.xlsx')  #分位数配置表
    for model_type in set(dfs.model_type.to_list()):

        cut_dfs = dfs.loc[dfs.model_type == model_type]

        q3 = config_table.loc[config_table.model_type == model_type,'q3'].values[0]
        q2 = config_table.loc[config_table.model_type == model_type,'q2'].values[0]
        q1 = config_table.loc[config_table.model_type == model_type,'q1'].values[0]



        # q3 = cut_dfs.score.quantile(0.9)
        # q2 = cut_dfs.score.quantile(0.7)
        # q1 = cut_dfs.score.quantile(0.3)

        cut_dfs.loc[cut_dfs.score > q3,'pjjg'] = 'A'

        cut_dfs.loc[(cut_dfs.score <= q3 ) & (cut_dfs.score > q2),'pjjg'] = 'B'

        cut_dfs.loc[(cut_dfs.score <= q2) & (cut_dfs.score > q1 ),'pjjg'] = 'C'

        cut_dfs.loc[cut_dfs.score <= q1 ,'pjjg']  = 'D'

        result = pd.concat([result,cut_dfs])

        result['pjjg_des'] = pd.NA

        result.loc[result['pjjg'] == 'A', 'pjjg_des'] = '企业主营业务产品绿色化水平或在行业内能耗强度表现较为突出，生产经营流程、内部管理机制等方面表现优秀，预期能够实现较高的环境与社会效益'
        result.loc[result['pjjg'] == 'B', 'pjjg_des'] = '企业主营业务产品绿色化水平或在行业内能耗强度表现较好，生产经营流程、内部管理机制等方面表现良好，预期能够实现相对较高的环境与社会效益'
        result.loc[result['pjjg'] == 'C', 'pjjg_des'] = '企业主营业务产品绿色化水平或在行业内能耗强度表现一般，生产经营流程、内部管理机制等方面表现一般，预期实现的环境与社会效益较弱'
        result.loc[result['pjjg'] == 'D', 'pjjg_des'] = '企业主营业务产品绿色化水平或在行业内能耗强度表现较差，生产流程、管理机制、经营水平等方面表现较差，预期难以实现环境与社会效益'
        print('文字描述添加结束')
    return result


def add_info(result,id_hander):
    all_result = pd.merge(id_hander,result,on='pjuuid',how='left')

    all_result['pjstatus']  =  pd.NA
    all_result.loc[all_result.score.notnull(),'pjstatus'] = 'S'
    all_result['pjstatus'] = all_result['pjstatus'].fillna('F')

    all_result['mdl_version'] = '2.0.1'
    all_result['info'] = pd.NA
    all_result.loc[all_result['pjstatus'] == 'F','info'] = 'data_error'

    all_result['create_time'] = time.strftime('%Y/%m/%d %H:%M',time.localtime())

    del all_result['esg_score']
    del all_result['rl_score']
    print('已删除')
    return all_result


def get_dff(lst,db):
    id_lst = str(lst)[1:-1]
    print(id_lst)


    sql_lst,sql_concat = db.get_sql_lst(id_lst)
    # sql_commands = '''
    #  select * from cs_mid_baseinfo_valid;
    # '''
    engine = db.engine
    with engine.connect() as connection:
        # 执行SQL语句并获取结果

        for sql in sql_lst:

            connection.execute(sql)


        result = connection.execute(sql_concat)
        # 将结果转换为DataFrame

        df = pd.DataFrame(result.fetchall())

        # 获取列名
        # df.columns = result.keys()
    # df = db.ReadSql(id_lst)
    print(df)
    return  df